/**
 * TO BE DETERMINED
 */
package org.eichar.server.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.eichar.client.shared.Language;
import org.eichar.client.shared.Word;
import org.eichar.server.i18n.Messages;

import com.google.inject.Inject;

/**
 * Provides access to the database.
 * <p>
 * In this implementation each language has a table with the name: CODE_WORDS
 * where code is a language code (en or fr). These tables list the words and an
 * id that identifies the word in a cross language context. The id is unique
 * within a table. Two words in two different tables have the same meaning. The
 * list of ids are in the {@link #TABLE_WORD_INDEX} table which also lists all
 * the languages that have a translation that maps to that index(as if that made
 * sense :-P ).
 * </p>
 * <p>
 * There is also a table that lists all the languages with their codes and the
 * identifiers. Identifiers are {@link Word} objects and as such can be in any
 * language. This table has three columns ( Code, identifier_codes,
 * identifier_text)
 * </p>
 * 
 * 
 * @author Jesse
 */
public class DatabaseAccess {

    private static final Logger LOGGER = Logger.getLogger(DatabaseAccess.class
            .getPackage().getName());

    // CONSTANTS FOR THE WORDS_TABLE
    // END OF TABLE NAME. Start is the language code
    private static final String WORD_TABLES_ENDING = "_WORDS"; //$NON-NLS-1$

    // COLUMN NAMES
    static final String COL_WORD_ID = "ID"; //$NON-NLS-1$

    static final String COL_WORD_WORD = "WORD"; //$NON-NLS-1$

    // CONSTANTS FOR THE LANGUAGES TABLE
    // TABLE NAME
    private static final String TABLE_LANGUAGES = "LANGUAGES"; //$NON-NLS-1$

    // COLUMN NAMES
    private static final String COL_LANGUAGES_CODE = "CODE"; //$NON-NLS-1$

    private static final String COL_LANGUAGES_ID_NAME = "IDENTIFIER_TEXT"; //$NON-NLS-1$

    private static final String COL_LANGUAGES_ID_CODES = "IDENTIFIER_CODES"; //$NON-NLS-1$

    // CONSTANTS FOR WORD INDEX TABLE
    // TABLE NAME
    private static final String TABLE_WORD_INDEX = "WORD_INDEX"; //$NON-NLS-1$

    // COLUMN NAMES
    private static final String COL_WORD_INDEX_WORD_ID = "WORD_ID"; //$NON-NLS-1$

    private static final String COL_WORD_INDEX_INSTANCES = "INSTANCES"; //$NON-NLS-1$

    /**
     * A where clause that removes the rows that are not translations. To be
     * used in conjunction with
     * {@link #createTranslationJoinSQL(String, String)}
     */
    private static final String REMOVE_NON_TRANSLATION_SQL = "WHERE T1." + COL_WORD_ID + " IS NOT NULL AND T2." + COL_WORD_ID + " IS NOT NULL"; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$

    private Connection dbConnection;

    /**
     * Creates new instance
     */
    @Inject
    public DatabaseAccess(Connection databaseConnection) {
        this.dbConnection = databaseConnection;
    }

    public void addLanguage(Language language) throws SQLException {
        Statement statement = getStatement();
        try {
            String tableName = toWordTableName(language.getCode());

            String createWordTable = "CREATE TABLE IF NOT EXISTS " + tableName + "(" + COL_WORD_ID + " BIGINT PRIMARY KEY," + COL_WORD_WORD + " VARCHAR(255));";//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
            statement.addBatch(createWordTable);
            String createLanguagesTable = createLanguagesTableSQL();
            statement.addBatch(createLanguagesTable);

            int[] results = statement.executeBatch();
            System.out.println(results);
            if (languageExists(statement, language)) {
                updateLanguage(language, statement);
            } else {
                mergeLanguage(language, statement);
            }

        } finally {
            statement.close();
        }
    }

    /**
     * @return
     */
    private String createLanguagesTableSQL() {
        String createLanguagesTable = "CREATE TABLE IF NOT EXISTS " + TABLE_LANGUAGES + " (" + COL_LANGUAGES_CODE + " VARCHAR(20) PRIMARY KEY, " + COL_LANGUAGES_ID_CODES + " ARRAY, " + COL_LANGUAGES_ID_NAME + " ARRAY);"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
        return createLanguagesTable;
    }

    private boolean languageExists(Statement statement, Language language)
            throws SQLException {
        statement.execute(createLanguagesTableSQL());
        String sql = "SELECT " + COL_LANGUAGES_CODE + " FROM " + TABLE_LANGUAGES + " WHERE " + COL_LANGUAGES_CODE + "=\'" + language.getCode() + "\'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
        return statement.executeQuery(sql).next();
    }

    private void updateLanguage(Language language, Statement statement)
            throws SQLException {
        String sql = "SELECT * FROM " + TABLE_LANGUAGES + " WHERE " + COL_LANGUAGES_CODE + "=\'" + language.getCode() + "\'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
        ResultSet results = statement.executeQuery(sql);
        results.next();

        Language newLang = parseLanguage(results);

        mergeLanguage(newLang, statement);
    }

    @SuppressWarnings("unchecked")
    private void mergeLanguage(Language language, Statement statement)
            throws SQLException {

        String identifierCodes = toCommaSeperatedCodes(language
                .getIdentifiers());
        String identifierText = toCommaSeperatedText(language.getIdentifiers());

        String mergeStatement = "MERGE INTO " + TABLE_LANGUAGES + " VALUES( \'" + language.getCode() + "\'," + identifierCodes + "," + identifierText + ")";//$NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
        statement.execute(mergeStatement);
    }

    public Collection<Language> getLanguages() throws SQLException {
        Statement statement = getStatement();
        try {
            statement.execute(createLanguagesTableSQL());
            ResultSet results = statement
                    .executeQuery("Select * from " + TABLE_LANGUAGES); //$NON-NLS-1$

            Collection<Language> languages = new HashSet<Language>();
            while (results.next()) {
                Language language = parseLanguage(results);
                languages.add(language);
            }

            return languages;
        } finally {
            statement.close();
        }
    }

    private Language parseLanguage(ResultSet results) throws SQLException {
        String languageCode = results.getString(COL_LANGUAGES_CODE);
        Object[] identifierCodes = (Object[]) results.getArray(
                COL_LANGUAGES_ID_CODES).getArray();
        Object[] identifierNames = (Object[]) results.getArray(
                COL_LANGUAGES_ID_NAME).getArray();

        Set<Word> identifiers = new HashSet<Word>();

        for (int i = 0; i < identifierCodes.length; i++) {
            String code = (String) identifierCodes[i];
            String name = (String) identifierNames[i];
            identifiers.add(Word.get(name, code));
        }
        Language language = Language.getLanguage(languageCode, identifiers);
        return language;
    }

    private String toCommaSeperatedText(Set<Word> identifiers) {
        if (identifiers.isEmpty()) {
            return "(\'\')"; //$NON-NLS-1$
        }
        StringBuilder builder = new StringBuilder();
        for (Word word : identifiers) {
            builder.append('\'');
            builder.append(word.getWord());
            builder.append('\'');
            builder.append(',');
        }
        return "(" + builder.substring(0, builder.length() - 1) + ")"; //$NON-NLS-1$//$NON-NLS-2$
    }

    private String toCommaSeperatedCodes(Set<Word> identifiers) {
        if (identifiers.isEmpty()) {
            return "(\'\')"; //$NON-NLS-1$
        }
        StringBuilder builder = new StringBuilder();
        for (Word word : identifiers) {
            builder.append('\'');
            builder.append(word.getLanguageCode());
            builder.append('\'');
            builder.append(',');
        }
        return "(" + builder.substring(0, builder.length() - 1) + ")"; //$NON-NLS-1$//$NON-NLS-2$
    }

    private Statement getStatement() throws SQLException {
        Statement statement = dbConnection.createStatement();
        if (LOGGER.isLoggable(Level.FINE)) {
            return new LoggingStatementDecorator(statement, LOGGER, Level.FINE);
        } else {
            return statement;
        }
    }

    /**
     * Adds the translation to the DB. Words must be in different languages
     * 
     * @param word1
     * @param word2
     * @throws SQLException
     */
    public void addTranslation(Word word1, Word word2) throws SQLException {
        assert !word1.getLanguageCode().equalsIgnoreCase(
                word2.getLanguageCode());

        Statement statement = getStatement();
        try {
            Collection<Language> languages = getLanguages();

            Language language1 = Language.getLanguage(word1.getLanguageCode());
            Language language2 = Language.getLanguage(word2.getLanguageCode());

            if (!languages.contains(language1)) {
                addLanguage(language1);
            }
            if (!languages.contains(language2)) {
                addLanguage(language2);
            }

            DBWord dbWord1 = getDBWord(word1, statement);
            DBWord dbWord2 = getDBWord(word2, statement);

            if (dbWord1 != null) {
                if (dbWord2 != null) {
                    if (dbWord2.getWordIndex() != dbWord1.getWordIndex()) {
                        Collection<String> affectedLanguages = langsWithWordID(
                                dbWord2.getWordIndex(), statement);
                        for (String language : affectedLanguages) {
                            updateWordTable(language, dbWord2.getWordIndex(),
                                    dbWord1.getWordIndex(), statement);
                        }
                        removeFromWordIndexTable(dbWord2.getWordIndex(),
                                statement);
                        addLangToInstanceList(affectedLanguages, dbWord1
                                .getWordIndex(), statement);
                    }
                } else {
                    addWord(word2, statement, dbWord1.getWordIndex());
                    addLangToInstanceList(Collections.singleton(word2
                            .getLanguageCode()), dbWord1.getWordIndex(),
                            statement);
                }
            } else {
                if (dbWord2 != null) {
                    addWord(word1, statement, dbWord2.getWordIndex());
                    addLangToInstanceList(Collections.singleton(word1
                            .getLanguageCode()), dbWord2.getWordIndex(),
                            statement);
                } else {
                    long wordIndex = nextWordIndex(statement, new String[] {
                            word1.getLanguageCode(), word2.getLanguageCode() });
                    addWord(word1, statement, wordIndex);
                    addWord(word2, statement, wordIndex);
                }
            }

        } finally {
            statement.close();
        }
    }

    private void removeFromWordIndexTable(long wordIndex, Statement statement)
            throws SQLException {
        String removeSQL = "DELETE FROM " + TABLE_WORD_INDEX + " WHERE " + COL_WORD_INDEX_WORD_ID + "=" + wordIndex; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
        boolean execute = statement.execute(removeSQL);
        assert execute;
    }

    /**
     * Changes the word id in the language table from oldWordIndex to
     * newWordIndex.
     */
    private void updateWordTable(String language, long oldWordIndex,
            long newWordIndex, Statement statement) throws SQLException {
        String updateSQL = "UPDATE " + toWordTableName(language) + " SET " + COL_WORD_ID + "=" + newWordIndex + " WHERE " + COL_WORD_ID + "=" + oldWordIndex; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$//$NON-NLS-5$

        int executeUpdate = statement.executeUpdate(updateSQL);
        assert executeUpdate == 1;
    }

    /**
     * Adds the languages of word2 to the {@link #COL_WORD_INDEX_INSTANCES}
     * column of the wordIndex row in the {@link #TABLE_WORD_INDEX} table.
     */
    private void addLangToInstanceList(Collection<String> langCodes,
            long wordIndex, Statement statement) throws SQLException {
        ResultSet result = statement
                .executeQuery(selectWordIndexInstancesArray(wordIndex));
        result.next();
        Object[] tableNames = (Object[]) result.getArray(1).getArray();
        StringBuilder stringBuilder = new StringBuilder("("); //$NON-NLS-1$
        for (Object object : tableNames) {
            stringBuilder.append("\'"); //$NON-NLS-1$
            stringBuilder.append(object);
            stringBuilder.append("\',"); //$NON-NLS-1$
        }
        for (String code : langCodes) {
            stringBuilder.append("\'"); //$NON-NLS-1$
            stringBuilder.append(code);
            stringBuilder.append("\',"); //$NON-NLS-1$
        }

        String newArray = stringBuilder
                .substring(0, stringBuilder.length() - 1)
                + ")"; //$NON-NLS-1$
        String updateSQL = "UPDATE " + TABLE_WORD_INDEX + " SET " + COL_WORD_INDEX_INSTANCES + "=" + newArray + " WHERE " + COL_WORD_INDEX_WORD_ID + "=" + wordIndex; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$//$NON-NLS-5$
        int executeUpdate = statement.executeUpdate(updateSQL);
        assert executeUpdate == 1;

    }

    /**
     * @return Returns all the languages that are listed as containing the word
     *         index. The information is contained in the
     *         {@link #TABLE_WORD_INDEX} table.
     * @throws SQLException
     */
    private Collection<String> langsWithWordID(long wordIndex,
            Statement statement) throws SQLException {
        ResultSet result = statement
                .executeQuery(selectWordIndexInstancesArray(wordIndex));
        if (result.next()) {
            Object[] tableNames = (Object[]) result.getArray(1).getArray();
            Set<String> returnValue = new HashSet<String>();
            for (Object object : tableNames) {
                returnValue.add((String) object);
            }
            return returnValue;
        }
        return Collections.emptySet();
    }

    /**
     * @param wordIndex
     * @return
     */
    private String selectWordIndexInstancesArray(long wordIndex) {
        String selectSQL = "SELECT " + COL_WORD_INDEX_INSTANCES + " FROM " + TABLE_WORD_INDEX + " WHERE " + COL_WORD_INDEX_WORD_ID + "=" + wordIndex; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$
        return selectSQL;
    }

    /**
     * Inserts a new row into the word index table.
     * 
     * @param languageCodes
     *            language codes of the language that will use the index.
     * @return next index
     * @throws SQLException
     */
    private long nextWordIndex(Statement statement, String[] languageCodes)
            throws SQLException {

        StringBuilder langCodesSQL = new StringBuilder("("); //$NON-NLS-1$
        for (String string : languageCodes) {
            langCodesSQL.append("\'"); //$NON-NLS-1$
            langCodesSQL.append(string);
            langCodesSQL.append("\'"); //$NON-NLS-1$
            langCodesSQL.append(',');
        }
        langCodesSQL.deleteCharAt(langCodesSQL.length() - 1);
        langCodesSQL.append(')');

        statement.addBatch(createWordIndexTableSQL());
        statement
                .addBatch("INSERT INTO " + TABLE_WORD_INDEX + " VALUES(DEFAULT,  " + langCodesSQL.toString() + " )"); //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
        statement.executeBatch();
        statement
                .execute("SELECT " + COL_WORD_INDEX_WORD_ID + " FROM " + TABLE_WORD_INDEX + " ORDER BY " + COL_WORD_INDEX_WORD_ID + " DESC"); //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$ //$NON-NLS-4$
        ResultSet resultSet = statement.getResultSet();
        resultSet.next();
        Long l = resultSet.getLong(COL_WORD_INDEX_WORD_ID);
        return l;
    }

    /**
     * @return
     */
    private String createWordIndexTableSQL() {
        return "CREATE TABLE IF NOT EXISTS " + TABLE_WORD_INDEX + " (" + COL_WORD_INDEX_WORD_ID + " BIGINT AUTO_INCREMENT, " + COL_WORD_INDEX_INSTANCES + " ARRAY)"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$;
    }

    /**
     * language table must exist before calling this method. Exception will be
     * thrown otherwise.
     * 
     * @return returns a DBWord if the word exists in the table. returns null
     *         otherwise.
     * @throws SQLException
     */
    private DBWord getDBWord(Word word1, Statement statement)
            throws SQLException {
        String wordTable = toWordTableName(word1.getLanguageCode());
        String findWordSQL = "SELECT * fROM " + wordTable + " WHERE " + COL_WORD_WORD + "=\'" + word1.getWord() + "\'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
        ResultSet result = statement.executeQuery(findWordSQL);
        if (result.next()) {
            DBWord word = new DBWord(word1.getLanguageCode(), result);
            return word;
        }
        return null;
    }

    /**
     * @param languageCode
     * @return
     */
    private String toWordTableName(String languageCode) {
        return languageCode.toUpperCase() + WORD_TABLES_ENDING;
    }

    private void addWord(Word word1, Statement statement, long wordIndex)
            throws SQLException {
        statement.addBatch(createWordIndexTableSQL());
        String langTableName = toWordTableName(word1.getLanguageCode());
        String addWordSQL = "INSERT INTO " + langTableName + " VALUES (" + wordIndex + ", \'" + word1.getWord() + "\')"; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$
        statement.addBatch(addWordSQL);
        statement.executeBatch();
    }

    /**
     * Returns the number of translations from languageCode1 to languageCode2.
     * 
     * @param language1
     *            one of the two languages to translate from->to
     * @param language2
     *            the other of the two languages to translate from->to
     * 
     * @return the number of translations from languageCode1 to languageCode2.
     * @throws SQLException
     */
    public int getTranslationCount(Language language1, Language language2)
            throws SQLException {
        Statement statement = dbConnection.createStatement();
        try {
            if (!languageExists(statement, language1)
                    || !languageExists(statement, language2)) {
                return 0;
            }

            String wordTable1 = toWordTableName(language1.getCode());
            String wordTable2 = toWordTableName(language2.getCode());

            String selectStmt = "SELECT COUNT(*) FROM " + createTranslationJoinSQL(wordTable1, wordTable2) + " " + REMOVE_NON_TRANSLATION_SQL; //$NON-NLS-1$ //$NON-NLS-2$ 

            ResultSet results = statement.executeQuery(selectStmt);

            if (results.next())
                return results.getInt(1);
            return 0;
        } finally {
            statement.close();
        }
    }

    /**
     * @param wordTable1
     *            one language table name
     * @param wordTable2
     *            another language table name
     * @return returns the table expression of the join of the two table. The
     *         join is on the id.
     */
    private String createTranslationJoinSQL(String wordTable1, String wordTable2) {
        String tableExpr = wordTable1
                + " AS T1 LEFT JOIN " + wordTable2 + " AS T2 ON T1." + COL_WORD_ID + "= T2." + COL_WORD_ID; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
        return tableExpr;
    }

    /**
     * Returns all the words for the given language. Use with care as it can be
     * an exensive operation.
     * 
     * @param language
     *            the language to look up.
     * @return all the words for the given language
     * @throws SQLException
     */
    public Collection<Word> getWords(Language language) throws SQLException {
        if (!getLanguages().contains(language)) {
            return Collections.emptyList();
        }

        String selectWordsSQL = "SELECT " + COL_WORD_WORD + " FROM " + toWordTableName(language.getCode()); //$NON-NLS-1$//$NON-NLS-2$
        Statement statement = dbConnection.createStatement();
        try {
            ResultSet results = statement.executeQuery(selectWordsSQL);

            List<Word> words = new LinkedList<Word>();
            while (results.next()) {
                words.add(Word.get(results.getString(1), language.getCode()));
            }
            return words;
        } finally {
            statement.close();
        }
    }

    /**
     * Returns the translation of <em>wordToTranslation</em> in the
     * <em>desiredLanguage</em> or null if the translations doesn't exist in
     * the DB
     * 
     * @param wordToTranslate
     * @param desiredLanguage
     *            a language code:
     * @return the translation of <em>wordToTranslation</em> in the
     *         <em>desiredLanguage</em> or null if the translations doesn't
     *         exist in the DB
     * @throws SQLException
     */
    public Word findTranslation(Word wordToTranslate, Language desiredLanguage)
            throws SQLException {
        Collection<Language> languages = getLanguages();
        if (!languages.contains(desiredLanguage)
                && languages.contains(Language.getLanguage(wordToTranslate
                        .getLanguageCode()))) {
            return null;
        }
        String wordTable2 = toWordTableName(desiredLanguage.getCode());
        String wordTable1 = toWordTableName(wordToTranslate.getLanguageCode());
        String joinSQL = createTranslationJoinSQL(wordTable1, wordTable2);
        String wordToTranslationSQL = " AND T1." + COL_WORD_WORD + "=\'" + wordToTranslate.getWord() + "\'"; //$NON-NLS-1$ //$NON-NLS-2$//$NON-NLS-3$
        String selectSQL = "SELECT T2." + COL_WORD_WORD + " FROM " + joinSQL + " " + REMOVE_NON_TRANSLATION_SQL + wordToTranslationSQL; //$NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
        Statement statement = dbConnection.createStatement();
        try {
            ResultSet results = statement.executeQuery(selectSQL);

            if (results.next()) {
                return Word
                        .get(results.getString(1), desiredLanguage.getCode());
            }
        } finally {
            statement.close();
        }
        return null;
    }

    /**
     * Returns the translation that is identified by the index. Essentially how
     * this works is the two word tables are joined and the resulting join is
     * sorted by wordId and the index translation is returned.
     * <p>
     * This is often used in conjunction with
     * {@link #getTranslationCount(Language, Language)}
     * </p>
     * 
     * @param index
     *            the index of the translation to return
     * @throws SQLException
     */
    public Word[] getTranslation(int index, Language lang1, Language lang2)
            throws SQLException {
        String joinSQL = createTranslationJoinSQL(toWordTableName(lang1
                .getCode()), toWordTableName(lang2.getCode()));

        String selectSQL = "SELECT T1." + COL_WORD_WORD + ", T2." + COL_WORD_WORD + " FROM " + joinSQL + " " + REMOVE_NON_TRANSLATION_SQL + " ORDER BY T1." + COL_WORD_ID + " ASC LIMIT 1 OFFSET " + index; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$//$NON-NLS-5$ //$NON-NLS-6$

        Statement statement = dbConnection.createStatement();

        try {
            ResultSet result = statement.executeQuery(selectSQL);

            if (result.next()) {
                Word[] translation = new Word[2];
                translation[0] = Word.get(result.getString(1), lang1.getCode());
                translation[1] = Word.get(result.getString(2), lang2.getCode());
                return translation;
            } else {
                throw new IndexOutOfBoundsException(Messages.getString(
                        "DatabaseAccess.outOfBounds", "" + index)); //$NON-NLS-1$ //$NON-NLS-2$
            }
        } finally {
            statement.close();
        }
    }

}
